using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using System.Globalization;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Common;
using Smo = Microsoft.SqlServer.Management.Smo;

using SqlWebAdmin.CSharp;

public partial class EditStoredProcedure : System.Web.UI.Page
{
  
    protected void Page_Load(object sender, EventArgs e)
    {
          // If not a PostBack
        if(!Page.IsPostBack)
        {

            
                lblError.Visible = false;

                // Create a new instance of the SMO Server class
				Smo.Server smoServer = SmoHelper.GetServer();
                try
                {
                    // Get the SMO Database 
                    String databaseName = Request.QueryString["database"];
                    Smo.Database smoDatabase = smoServer.Databases[databaseName];
                    if(smoDatabase != null)
                    {

                        // Get the Stored Procedure name from the URL
                        String storedProcedureName = Request.QueryString["storedprocedure"];
                        String schemaName = Request.QueryString["schema"];

                        // If Stored Procedure name is NOT in the Database
                        if(smoDatabase.StoredProcedures.Contains(storedProcedureName, schemaName) == false)
                        {

                            // Let the user know we are adding a new Stored Procedure
                            lblTitle.Text = "Add Stored Procedure";
                            cmdSave.Text = "Save";
                            cmdSave.Enabled = true;

                            // Show the name of the new Stored Procedure
                            lblStoredProcedureName.Text = schemaName + "." + storedProcedureName;
                            lblStoredProcedureCreateDate.Text = String.Empty;

                         }
                        else
                        {
                            // Get the Stored Procedure
                            Smo.StoredProcedure smoStoredProcedure = smoDatabase.StoredProcedures[storedProcedureName,schemaName];

                            if(smoStoredProcedure == null)
                            {
                                Response.Redirect(String.Format("error.aspx?error={0}", Globals.storedProcedureNotFoundErrorNumber), false);
                            }
                            else
                            {
                                if(smoStoredProcedure.IsSystemObject)
                                {
                                    // Let the user know we are editing an existing column
                                    lblTitle.Text = "View System Stored Procedure";
                                    cmdSave.Enabled = false;
                                }
                                else
                                {
                                    // Let the user know we are editing an existing column
                                    lblTitle.Text = "Edit Stored Procedure";
                                    cmdSave.Text = "Save";
                                    cmdSave.Enabled = true;
                                }

                                // Show the Stored Procedure's attributes
                                lblStoredProcedureName.Text = HttpUtility.HtmlEncode(smoStoredProcedure.Schema) + "." + HttpUtility.HtmlEncode(smoStoredProcedure.Name);
                                lblStoredProcedureCreateDate.Text = HttpUtility.HtmlEncode(Convert.ToString(smoStoredProcedure.CreateDate));
                                                                // Show the body of the Stored Procedure
                                txtTextBody.Text = smoStoredProcedure.TextHeader +  System.Environment.NewLine + smoStoredProcedure.TextBody;
								
                            }
                        }
                    }
                    else
                    {
                        Response.Redirect(String.Format("error.aspx?error={0}", Globals.databaseNotFoundErrorNumber), false);
                    }
                }
                catch(Microsoft.SqlServer.Management.Smo.SmoException smoEx)
                {
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(smoEx));
                    lblError.Visible = true;
                }
                catch(System.Exception ex)
                {
                    lblError.Visible = true;
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(ex));
                }
            
        }
    }

    protected void btnCheckSyntax_Click(Object sender, System.EventArgs e)
    {

        // If the page is valid
        if(Page.IsValid)
        {

               lblError.Visible = false;

                // Create a new instance of the SMO Server class
				Smo.Server smoServer = SmoHelper.GetServer();
                try
                {
                    // Get the SMO Database 
                    String databaseName = Request.QueryString["database"];
                    Smo.Database smoDatabase = smoServer.Databases[databaseName];
                    if(smoDatabase != null)
                    {

                        // Get the stored procedure name
                        String storedProcedureName = Request.QueryString["storedprocedure"];
                        String schemaName = Request.QueryString["schema"];

                        // Check if the stored procedure already exists
                        Smo.StoredProcedure smoStoredProcedure = null;

                        // If this is an exising Stored Procedure
                        if(smoDatabase.StoredProcedures.Contains(storedProcedureName, schemaName))
                        {

                            // Get the existing Stored Procedure
                            smoStoredProcedure = smoDatabase.StoredProcedures[storedProcedureName];

                            // Get the updated BodyText
                            smoStoredProcedure.TextBody = txtTextBody.Text;

                            // Get the complete SQL Script for the Stored Procedure
                            smoServer.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;
                            smoServer.ConnectionContext.CapturedSql.Clear();
                            smoStoredProcedure.Alter();
                            StringCollection sqlTextCollection = smoServer.ConnectionContext.CapturedSql.Text;
                            smoServer.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql;

                            // Check the SQL Script syntax
                            smoServer.ConnectionContext.ExecuteNonQuery(sqlTextCollection, ExecutionTypes.NoExec);

                        }
                        else
                        {
    
                            // Get the new BodyText
                            smoStoredProcedure.TextBody = txtTextBody.Text;

                            // Check the SQL Script syntax
							smoServer.ConnectionContext.ExecuteNonQuery(txtTextBody.Text, ExecutionTypes.NoExec);
                            //smoServer.ConnectionContext.ExecuteNonQuery(sqlTextCollection, ExecutionTypes.NoExec);
                        }

                        lblError.Visible = true;
                        lblError.Text = "Syntax check successfull.";
                    }
                    else
                    {
                        Response.Redirect(String.Format("error.aspx?error={0}", Globals.databaseNotFoundErrorNumber), false);
                    }

                }
                catch(Microsoft.SqlServer.Management.Common.ExecutionFailureException execEx)
                {
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(execEx));
                    lblError.Visible = true;
                }
                catch(Microsoft.SqlServer.Management.Smo.SmoException smoEx)
                {
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(smoEx));
                    lblError.Visible = true;
                }
                catch(System.Exception ex)
                {
                    lblError.Visible = true;
                    lblError.Text = HttpUtility.HtmlEncode(Common.ConcatenateExceptions(ex));
                }
                finally
                {
                    try
                    {
                        // TODO: REMOVE -- Beta1 bug - Must turn NOEXEC off manually if exception occurs
                        smoServer.ConnectionContext.ExecuteNonQuery("SET NOEXEC OFF");
                    }
                    catch{}
                }
            
        }
    }

    protected void cmdSave_Click(Object sender, System.EventArgs e)
    {
        // If the page is valid
        if(Page.IsValid)
        {

           
                lblError.Visible = false;

                // Create a new instance of the SMO Server class
				Smo.Server smoServer = SmoHelper.GetServer();
				try
				{
					SmoHelper.ResetServer(ref smoServer);

					// Get the SMO Database 
					String databaseName = Request.QueryString["database"];
					Smo.Database smoDatabase = smoServer.Databases[databaseName];
					if (smoDatabase != null)
					{
						smoServer.ConnectionContext.BeginTransaction();

						// Get the stored procedure name
						String storedProcedureName = Request.QueryString["storedprocedure"];
						String schemaName = Request.QueryString["schema"];

						// Check if the stored procedure already exists
						Smo.StoredProcedure smoStoredProcedure = null;
						smoDatabase.StoredProcedures.Refresh();
						bool storedProcedureExists = smoDatabase.StoredProcedures.Contains(storedProcedureName);

						// If this is an exising Stored Procedure
						if (storedProcedureExists)
						{

							// Drop the Stored Procedure (we'll recreate it again below)
							smoStoredProcedure = smoDatabase.StoredProcedures[storedProcedureName];
							smoStoredProcedure.Drop();
						}

						smoDatabase.ExecuteNonQuery(txtTextBody.Text);

						
						smoServer.ConnectionContext.CommitTransaction();
						// Return back to the Database page
						Response.Redirect(String.Format("StoredProcedures.aspx?database={0}", HttpUtility.UrlEncode(databaseName)), false);
					}
					else
					{
						Response.Redirect(String.Format("error.aspx?error={0}", Globals.databaseNotFoundErrorNumber), false);
					}

				}
				catch (Microsoft.SqlServer.Management.Smo.SmoException smoEx)
				{
					lblError.Text =  Common.ConcatenateExceptions(smoEx) ;
					lblError.Visible = true;
					smoServer.ConnectionContext.RollBackTransaction();
				}
				catch (System.Exception ex)
				{
					lblError.Visible = true;
					lblError.Text = Common.ConcatenateExceptions(ex) ;
					smoServer.ConnectionContext.RollBackTransaction();
				}
				finally
				{
					smoServer.ConnectionContext.Disconnect();
				}
            
        }
    }

    private void cmdCancel_Click(Object sender, System.EventArgs e)
    {
        // Redirect back to the Database page
        Response.Redirect(("StoredProcedures.aspx?database=" + HttpUtility.UrlEncode(Request.QueryString["database"])), false);
    }

    
 
 
}
